using System.Text;
using Dapper;
using XchUserInfo.Common;
using XchUserInfo.Model;
using System.Threading.Tasks;
using System.Collections.Generic;
using System.Linq;
using System;
using System.Data;

namespace XchUserInfo.DAL
{
    [Service(EServLife.Scoped)]
    public class XchUserDAO : IXchUserDAO
    {
        private readonly IConnectionFactory _connectionFactory;
        public XchUserDAO(IConnectionFactory connectionFactory)
        {
            _connectionFactory = connectionFactory;
        }

        public async Task<XchUser?> GetAsync(int id)
        {
            const string sql = @"SELECT XchUserID, XchUserName,
                                        XchEmail  AS XchUserEmail,
                                        XchPhone  AS XchUserPhone,
                                        XchAddress AS XchUserAddress,
                                        XchRegDateTime
                                 FROM dbo.XchUserInfo WHERE XchUserID = @id";
            using var conn = _connectionFactory.GetConnection();
            return await conn.QueryFirstOrDefaultAsync<XchUser>(sql, new { id });
        }

        public async Task<int> InsertAsync(XchUser entity)
        {
            const string sql = @"INSERT INTO dbo.XchUserInfo (XchUserName, XchUserPassword, XchEmail, XchPhone, XchAddress)
                                 VALUES (@XchUserName, @XchUserPassword, @XchUserEmail, @XchUserPhone, @XchUserAddress);
                                 SELECT CAST(SCOPE_IDENTITY() as int);";
            using var conn = _connectionFactory.GetConnection();
            var newId = await conn.ExecuteScalarAsync<int>(sql, entity);
            return newId;
        }

        public async Task<bool> UpdateAsync(XchUser entity)
        {
            const string sql = @"UPDATE dbo.XchUserInfo
                                 SET XchUserName=@XchUserName,
                                     XchUserPassword=@XchUserPassword,
                                     XchEmail=@XchUserEmail,
                                     XchPhone=@XchUserPhone,
                                     XchAddress=@XchUserAddress
                                 WHERE XchUserID=@XchUserID";
            using var conn = _connectionFactory.GetConnection();
            var rows = await conn.ExecuteAsync(sql, entity);
            return rows > 0;
        }

        public async Task<bool> DeleteAsync(int id)
        {
            const string sql = @"DELETE FROM dbo.XchUserInfo WHERE XchUserID=@id";
            using var conn = _connectionFactory.GetConnection();
            var rows = await conn.ExecuteAsync(sql, new { id });
            return rows > 0;
        }

        public async Task<PagedList<XchUser>> PageAsync(int pageIndex, int pageSize, string? nameLike, string? emailLike, string? phoneLike)
        {
            var where = new StringBuilder("WHERE 1=1");
            var parameters = new DynamicParameters();
            if (!string.IsNullOrWhiteSpace(nameLike)) { where.Append(" AND XchUserName LIKE @name"); parameters.Add("name", $"%{nameLike}%"); }
            if (!string.IsNullOrWhiteSpace(emailLike)) { where.Append(" AND XchEmail LIKE @email"); parameters.Add("email", $"%{emailLike}%"); }
            if (!string.IsNullOrWhiteSpace(phoneLike)) { where.Append(" AND XchPhone LIKE @phone"); parameters.Add("phone", $"%{phoneLike}%"); }

            var sqlCount = $"SELECT COUNT(1) FROM dbo.XchUserInfo {where}";
            var sqlPage = $@"SELECT XchUserID, XchUserName,
                                     XchEmail AS XchUserEmail,
                                     XchPhone AS XchUserPhone,
                                     XchAddress AS XchUserAddress,
                                     XchRegDateTime
                             FROM dbo.XchUserInfo {where}
                             ORDER BY XchUserID DESC
                             OFFSET @offset ROWS FETCH NEXT @ps ROWS ONLY";

            using var conn = _connectionFactory.GetConnection();
            var total = await conn.ExecuteScalarAsync<int>(sqlCount, parameters);
            parameters.Add("offset", (pageIndex - 1) * pageSize);
            parameters.Add("ps", pageSize);
            var list = (await conn.QueryAsync<XchUser>(sqlPage, parameters)).ToList();
            return new PagedList<XchUser>(list, pageIndex - 1, pageSize, total);
        }
    }
}



